package com.yzdbx.task;

import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.util.RandomUtil;
import com.yzdbx.utils.DbUtil;
import com.yzdbx.utils.GenUtil;

import java.math.BigDecimal;
import java.sql.*;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

import static com.yzdbx.task.ProductPlanRunnable.GEN_PRODUCT_PLAN_IDS;
import static com.yzdbx.task.SalefOrderRunnable.GEN_ORDER_IDS;
import static com.yzdbx.utils.DbUtil.getMaxId;
import static com.yzdbx.utils.GenUtil.CUR_TIME_STAMP;
import static com.yzdbx.utils.GenUtil.getNo;

/**
 * @BelongsProject: SERVEVICE-DATA-GEN
 * @BelongsPackage: com.yzdbx.task
 * @Author: lk
 * @CreateTime: 2025-05-14
 * @Description: TODO
 * @Version: 1.0
 */
@SuppressWarnings("all")
public class ProductOrderRunnable implements Exe {

    private Connection connection;
    private static final int BATCH_SIZE = 3;


    public static final ThreadLocal<List<Integer>> GEN_PRODUCT_ORDER_IDS = new ThreadLocal<>() {
        @Override
        protected List<Integer> initialValue() {
            return new ArrayList<>();
        }
    };

    public static final ThreadLocal<List<Integer>> GEN_LLD_IDS = new ThreadLocal<>() {
        @Override
        protected List<Integer> initialValue() {
            return new ArrayList<>();
        }
    };

    public ProductOrderRunnable(Connection connection) {
        this.connection = connection;
    }

    public boolean execute(int count) throws SQLException {
        //生产计划 ---> 生产工单
        List<Integer> planIds = GEN_PRODUCT_PLAN_IDS.get();
        int batchCount = 0;

        if (CollectionUtil.isEmpty(planIds)) return Boolean.FALSE;

        List<Integer> orderIds = new ArrayList<>(planIds.size());
        List<Integer> lldIds = new ArrayList<>(planIds.size());

        // 查询员工信息
        List<String> members;
        try (PreparedStatement statement1 = connection.prepareStatement("select * from corp_member")) {
            members = DbUtil.resultSetToList(statement1.executeQuery()).stream()
                    .map(map -> map.get("memberId").toString()).toList();
        }

        int rwId = getMaxId(connection, "missionInformationSc02");
        int num = 0;
        int num1 = 0;
        int num2 = 0;
        int num3 = 0;

        //查询工艺价格
        Map<String, BigDecimal> collect;
        try (PreparedStatement preparedStatement = connection.prepareStatement("select * from processBasicTables")) {
            collect = DbUtil.resultSetToList(preparedStatement.executeQuery()).stream()
                    .collect(Collectors.toMap(cur -> cur.get("processName").toString(),
                            cur -> ((BigDecimal) cur.get("unitPrice"))));
        }

        int lldId = getMaxId(connection, "itsACollarList");
        int lldxqId = getMaxId(connection, "checklistOfTheCollar");
        int gyId = getMaxId(connection, "sctechMissions");
        int bgId = getMaxId(connection, "checklistSc05");

        // 准备批量插入语句
        String missionSql = "insert into missionInformationSc02(id,missionStream,productName,otherOrganiser,endOfSchedule,dateOfDelivery,numberOfTasks,numberCompleted,officerincharge,productionPlan,taskStatus,typeOfWorksheet,synchronisingFolderFailedSS,created,actualEndDate,f271) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
        String collarSql = "insert into itsACollarList (id,theCollarNumber,associatedProducts,productionTasks,f775,theLeadman,associatedOrders,numberToBeProduced) values (?,?,?,?,?,?,?,?)";
        String collarDetailSql = "insert into checklistOfTheCollar (id,f8d0,materialInformation,number)values (?,?,?,?)";
        String techMissionSql = "insert into sctechMissions (id,craftFlowNumber,associatedTasks,processName,serialNumber,productInformation,dateOfDelivery,plannedEndDate,numberOfTasks,numberCompleted,mandatedPersons,routineStatus)values (?,?,?,?,?,?,?,?,?,?,?,?)";
        String reportSql = "insert into checklistSc05 (id,wellCallTheFactoryStreamer,linkProcessTasks,associatedProductionTasks,productInformation,processName,dateOfReport,numberOfTasks,numberOfCompleted,numberOfReporters,numberOfEndoflifeWarranties,reportingStaff,auditStatus,unitPrice,synchronisingFolderFailedSS,created,modifyer,modified) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
        String updateCollarSql = "update itsACollarList set totalNumber = ? where id = ?";

        try (PreparedStatement missionStatement = connection.prepareStatement(missionSql);
             PreparedStatement collarStatement = connection.prepareStatement(collarSql);
             PreparedStatement collarDetailStatement = connection.prepareStatement(collarDetailSql);
             PreparedStatement techMissionStatement = connection.prepareStatement(techMissionSql);
             PreparedStatement reportStatement = connection.prepareStatement(reportSql);
             PreparedStatement updateCollarStatement = connection.prepareStatement(updateCollarSql)) {


            for (Integer planId : planIds) {
                Map<String, Object> productPlan = DbUtil.getById(connection, "sc01ProductionPlan", planId);

                // 创建生产工单
                missionStatement.setInt(1, ++rwId);
                orderIds.add(rwId);
                missionStatement.setString(2, getNo("RW", ++num));
                missionStatement.setInt(3, ((int) productPlan.get("productInformation")));
                missionStatement.setDate(4, ((Date) productPlan.get("dateOfDelivery")));
                missionStatement.setDate(5, ((Date) productPlan.get("dateOfDelivery")));
                missionStatement.setDate(6, ((Date) productPlan.get("dateOfDelivery")));
                missionStatement.setBigDecimal(7, ((BigDecimal) productPlan.get("number")));
                missionStatement.setBigDecimal(8, ((BigDecimal) productPlan.get("number")));
                missionStatement.setString(9, productPlan.get("synchronisingFolderFailedSS").toString());
                missionStatement.setInt(10, planId);
                missionStatement.setString(11, "未开始");
                missionStatement.setString(12, "存储内容2");
                missionStatement.setString(13, productPlan.get("synchronisingFolderFailedSS").toString());
                missionStatement.setTimestamp(14, ((Timestamp) productPlan.get("created")));
                missionStatement.setDate(15, ((Date) productPlan.get("dateOfDelivery")));
                missionStatement.setString(16, "已排程");
                missionStatement.addBatch();

                //生产工单领料单
                collarStatement.setInt(1, ++lldId);
                lldIds.add(lldId);
                collarStatement.setString(2, getNo("LL", ++num3));
                collarStatement.setInt(3, ((int) productPlan.get("productInformation")));
                collarStatement.setInt(4, rwId);
                collarStatement.setDate(5, ((Date) productPlan.get("dateOfDelivery")));
                collarStatement.setString(6, productPlan.get("synchronisingFolderFailedSS").toString());
                collarStatement.setInt(7, planId);
                collarStatement.setBigDecimal(8, ((BigDecimal) productPlan.get("number")));
                collarStatement.addBatch();

                //生产领料单明细
                List<LinkedHashMap<String, Object>> lldList;
                try (PreparedStatement statement = connection.prepareStatement("select * from productBom where rawMaterials = ?")) {
                    statement.setInt(1, ((int) productPlan.get("productInformation")));
                    lldList = DbUtil.resultSetToList(statement.executeQuery());
                }

                BigDecimal total = BigDecimal.ZERO;
                for (LinkedHashMap<String, Object> lld : lldList) {
                    collarDetailStatement.setInt(1, ++lldxqId);
                    collarDetailStatement.setInt(2, lldId);
                    collarDetailStatement.setInt(3, ((int) lld.get("rawMaterials1")));
                    BigDecimal quantity = ((BigDecimal) lld.get("number")).multiply(((BigDecimal) productPlan.get("number"))).setScale(2, BigDecimal.ROUND_HALF_UP);
                    collarDetailStatement.setBigDecimal(4, quantity);
                    collarDetailStatement.addBatch();
                    total = total.add(quantity);
                }

                //更新领料单总数量
                updateCollarStatement.setBigDecimal(1, total);
                updateCollarStatement.setInt(2, lldId);
                updateCollarStatement.addBatch();

                //查询产品工艺路线
                List<LinkedHashMap<String, Object>> gylxList;
                try (PreparedStatement statement = connection.prepareStatement("select * from gy0501ProcessProcessSubtable where associatedProductLibrary = ? order by id asc")) {
                    statement.setInt(1, ((int) productPlan.get("productInformation")));
                    gylxList = DbUtil.resultSetToList(statement.executeQuery());
                }

                for (LinkedHashMap<String, Object> gylx : gylxList) {
                    //工单生成工艺任务
                    techMissionStatement.setInt(1, ++gyId);
                    techMissionStatement.setString(2, getNo("GY", ++num1));
                    techMissionStatement.setInt(3, rwId);
                    techMissionStatement.setString(4, gylx.get("f7ff").toString());
                    techMissionStatement.setInt(5, ((int) gylx.get("id")));
                    techMissionStatement.setInt(6, ((int) productPlan.get("productInformation")));
                    techMissionStatement.setDate(7, ((Date) productPlan.get("dateOfDelivery")));
                    techMissionStatement.setDate(8, ((Date) productPlan.get("dateOfDelivery")));
                    techMissionStatement.setBigDecimal(9, ((BigDecimal) productPlan.get("number")));
                    techMissionStatement.setBigDecimal(10, ((BigDecimal) productPlan.get("number")));
                    techMissionStatement.setString(11, productPlan.get("synchronisingFolderFailedSS").toString());
                    techMissionStatement.setString(12, "存储内容2");
                    techMissionStatement.addBatch();

                    //生成报工明细
                    reportStatement.setInt(1, ++bgId);
                    reportStatement.setString(2, getNo("BG", ++num2));
                    reportStatement.setInt(3, gyId);
                    reportStatement.setInt(4, rwId);
                    reportStatement.setInt(5, ((int) productPlan.get("productInformation")));
                    reportStatement.setString(6, gylx.get("f7ff").toString());
                    reportStatement.setDate(7, ((Date) productPlan.get("dateOfDelivery")));
                    reportStatement.setBigDecimal(8, ((BigDecimal) productPlan.get("number")));
                    //随机完成数量
                    BigDecimal bigDecimal = RandomUtil.randomBigDecimal(BigDecimal.ONE, ((BigDecimal) productPlan.get("number")));
                    reportStatement.setBigDecimal(9, bigDecimal);
                    reportStatement.setBigDecimal(10, bigDecimal);
                    reportStatement.setBigDecimal(11, BigDecimal.ZERO);
                    reportStatement.setString(12, RandomUtil.randomEle(members));
                    reportStatement.setString(13, "已审核");
                    reportStatement.setBigDecimal(14, collect.get(gylx.get("f7ff").toString()));
                    reportStatement.setString(15, productPlan.get("synchronisingFolderFailedSS").toString());
                    reportStatement.setTimestamp(16, ((Timestamp) productPlan.get("created")));
                    reportStatement.setString(17, productPlan.get("synchronisingFolderFailedSS").toString());
                    reportStatement.setTimestamp(18, ((Timestamp) productPlan.get("created")));
                    reportStatement.addBatch();
                }

                batchCount++;

                // 每BATCH_SIZE条记录执行一次批量插入
                if (batchCount >= BATCH_SIZE) {
                    missionStatement.executeBatch();
                    collarStatement.executeBatch();
                    collarDetailStatement.executeBatch();
                    updateCollarStatement.executeBatch();
                    techMissionStatement.executeBatch();
                    reportStatement.executeBatch();
                    batchCount = 0;
                }
            }

            // 执行剩余的批量插入
            if (batchCount > 0) {
                missionStatement.executeBatch();
                collarStatement.executeBatch();
                collarDetailStatement.executeBatch();
                updateCollarStatement.executeBatch();
                techMissionStatement.executeBatch();
                reportStatement.executeBatch();
            }
        }

        GEN_PRODUCT_ORDER_IDS.set(orderIds);
        GEN_LLD_IDS.set(lldIds);
        return Boolean.TRUE;
    }
}
